package com.zjmzxfzhl.common.util.bs;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import cn.hutool.poi.excel.StyleSet;
import cn.hutool.poi.excel.cell.CellUtil;
import cn.hutool.poi.excel.style.StyleUtil;
import com.zjmzxfzhl.common.exception.AppException;
import com.zjmzxfzhl.common.exception.BaseException;
import com.zjmzxfzhl.modules.purchase.controller.vo.PurchaseOrderDetailVo;
import com.zjmzxfzhl.modules.purchase.controller.vo.PurchaseProjectVo;
import com.zjmzxfzhl.modules.purchase.entity.PurchaseOrder;
import com.zjmzxfzhl.modules.purchase.service.bo.PaymentApprovalInfo;
import com.zjmzxfzhl.modules.purchase.service.bo.PurchaseMaterialBo;
import com.zjmzxfzhl.modules.purchase.service.bo.PurchaseOrderExcelBo;
import com.zjmzxfzhl.modules.purchase.service.bo.PurchaseSupplierDetailExcelBo;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import java.io.File;
import java.math.BigDecimal;
import java.sql.Date;
import java.text.MessageFormat;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;

public class PurchaseUtils {
    public static String PURCHASE_ORDER_START = "采购审批表";
    public static String PURCHASE_ORDER_END1 = "领导审批";
    public static String PURCHASE_ORDER_END2 = "商务部";
    public static String PURCHASE_ORDER_DATE = "日期";
    public static DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy年M月d日");
    public static List<String> detailFieldAry = Arrays.asList("orderCode", "orderDate", "projectName", "supplierName", "contractCode", "indentCode", "invoice", "paymentSituation", "contractSubAmount", "materialName", "materialSpecification", "unit", "unitPrice", "materialNum", "materialTotalPrice");
    public static HashMap<String, String> orderTitleMap = new HashMap<String, String>() {{
        put("序号", "xh");
        put("物料编码", "equipmentCode");
        put("器材设备名称", "equipmentName");
        put("型号", "specification");
        put("单位", "unit");
        put("数量", "equipmentNum");
        put("单价", "equipmentUnitPrice");
        put("总价", "equipmentTotalPirce");
        put("用途", "orderUsage"); //项目 projectName
        put("采购订单编号", "indentCode");
        put("付款", "inventory"); //由库存改成付款 2020-11-23
        put("供货方", "supplier");
        put("备注", "comment");

    }};

    public static HashMap<String, String> supplierTitleMap = new HashMap<String, String>() {{
        put("审批单编号", "orderCode");
        put("审批单日期", "orderDate");
        put("用途", "orderUsage"); //项目 projectName
        put("合同编号", "contractCode");
        put("合同状态", "contractState");
        put("到货情况", "aogSituation");
        put("发票", "invoice");
        put("付款情况", "paymentSituation");
        put("器材设备名称", "materialName");
        put("型号", "materialSpecification");
        put("单价", "unitPrice");
        put("数量", "materialNum");
        put("总价", "materialTotalPrice");
    }};

    public static HashMap<String, String> projectTitleMap = new HashMap<String, String>() {{
        put("审批单编号", "orderCode");
        put("审批单日期", "orderDate");
        put("供应商", "supplierName"); //项目 projectName
        put("合同编号", "contractCode");
        put("合同状态", "contractState");
        put("到货情况", "aogSituation");
        put("发票", "invoice");
        put("付款情况", "paymentSituation");
        put("器材设备名称", "materialName");
        put("型号", "materialSpecification");
        put("单价", "unitPrice");
        put("数量", "materialNum");
        put("总价", "materialTotalPrice");
    }};

    public static HashMap<String, String> materialTitleMap = new HashMap<String, String>() {{
        put("产品名称", "materialName");
        put("型号", "materialSpecification");
        put("单位", "unit");
        put("数量", "materialNum");
        put("单价", "unitPrice");
        put("总价", "materialTotalPrice");
    }};

    public static void exportIndent(String indentCode, String supplierName, String projectName, List<PurchaseMaterialBo> materialBos, File targetFile) {
        int startRow = 8;
        int startCol = 1;
        ExcelWriter writer = ExcelUtil.getWriter(targetFile);
        //设置样式
        StyleSet styleSet = createDefaultStyleSet(writer);
        styleSet.setFont((short) -1, (short) 11, "宋体", true);
        writer.setStyleSet(styleSet);

        Cell headerCell = writer.getCell(startCol, startRow + 1, true);
//        headerCell.setCellValue(headerCell.getStringCellValue().replace("${supplierName}", supplierName));
        HSSFRichTextString richString = null;
        richString = new HSSFRichTextString(headerCell.getStringCellValue().replace("${supplierName}", supplierName));
        richString.applyFont(10, 10 + supplierName.length(), getUnderlineFont(writer.getWorkbook()));  //下划线的起始位置，结束位置
        richString.applyFont(10 + supplierName.length(), richString.length(), StyleUtil.createFont(writer.getWorkbook(), (short) -1, (short) 14, "宋体"));
        headerCell.setCellValue(richString);

        Cell indentCell = writer.getCell(startCol, startRow + 3, true);
        indentCell.setCellValue(indentCell.getStringCellValue().replace("${indentCode}", indentCode));

        Cell projectCell = writer.getCell(startCol + 3, startRow + 3, true);
        projectCell.setCellValue(projectCell.getStringCellValue().replace("${projectName}", projectName));
        int headerRowIndex = startRow + 4;
        //插入内容
        if (!CollectionUtils.isEmpty(materialBos)) {
            Sheet sheet = writer.getSheet();
            Row headerRow = writer.getOrCreateRow(headerRowIndex);
            sheet.shiftRows(headerRowIndex + 1, sheet.getLastRowNum(), materialBos.size(), true, false);
            int i = headerRowIndex + 1;
            BigDecimal totalPrice = new BigDecimal(0);
            for (PurchaseMaterialBo materialBo : materialBos) {
                Row dataRow = writer.getOrCreateRow(i);
                dataRow.setHeight((short) 554);
                //写入行数据
                insertRowData(dataRow, headerRow, materialBo, materialTitleMap, writer.getCellStyle());
                totalPrice = totalPrice.add(materialBo.getMaterialTotalPrice());
                i++;
            }

            //替换totalNoTaxPrice
            Cell totalNoTaxPriceCell = writer.getCell(startCol + 5, startRow + 7 + materialBos.size(), true);
            BigDecimal totalNoTaxPrice = totalPrice.divide(new BigDecimal(1.13), 2, BigDecimal.ROUND_HALF_UP);
            totalNoTaxPriceCell.setCellValue(totalNoTaxPriceCell.getStringCellValue().replace("${totalNoTaxPrice}", totalNoTaxPrice.toString()));

            //替换totalPrice
            Cell totalPriceCell = writer.getCell(startCol + 5, startRow + 8 + materialBos.size(), true);
            totalPriceCell.setCellValue(totalPriceCell.getStringCellValue().replace("${totalPrice}", totalPrice.toString()));

            //合并备注
            int commentCol = startCol + 6;
//            writer.merge(headerRowIndex + 1, headerRowIndex + 3 + materialBos.size(), commentCol, commentCol, "将货物送货至袍江，并将货物搬进仓库5楼（有电梯）内，周末节假日不要送货。", false);
            CellStyle cellStyle = selfCellStyle(writer, BorderStyle.THIN, IndexedColors.BLACK, (short) -1, (short) 10, "等线", VerticalAlignment.CENTER, true);
            cellStyle.setBorderRight(BorderStyle.MEDIUM);
            CellUtil.mergingCells(writer.getSheet(), headerRowIndex + 1, headerRowIndex + 3 + materialBos.size(), commentCol, commentCol, cellStyle);
            Cell commentCell = writer.getCell(commentCol, headerRowIndex + 1);
            CellUtil.setCellValue(commentCell, "将货物送货至袍江，并将货物搬进仓库5楼（有电梯）内，周末节假日不要送货。", cellStyle);
        } else {
            //清空totalPrice
            Cell totalPriceCell = writer.getCell(startCol + 5, startRow + 8 + materialBos.size(), true);
            totalPriceCell.setCellValue(totalPriceCell.getStringCellValue().replace("${totalPrice}", ""));
        }

        //替换卖方
        Cell sellerCell = writer.getCell(4, 30 + materialBos.size(), true);
        sellerCell.setCellValue(sellerCell.getStringCellValue().replace("${supplierName}", supplierName));

        //替换日期
        DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy.M.d");
        Cell dateCell = writer.getCell(startCol, 39 + materialBos.size(), true);
        dateCell.setCellValue(dateCell.getStringCellValue().replace("${curDate}", dtf.format(LocalDate.now())));

        writer.close();
    }

    public static CellStyle selfCellStyle(ExcelWriter writer,
                                          BorderStyle borderStyle,
                                          IndexedColors colors,
                                          short color,
                                          short fontSize,
                                          String fontName,
                                          VerticalAlignment verticalAlignment,
                                          Boolean wrapText) {
        CellStyle cellStyle = writer.getWorkbook().createCellStyle();
        StyleUtil.setBorder(cellStyle, borderStyle, colors);
        cellStyle.setVerticalAlignment(verticalAlignment);
//        cellStyle.setBorderRight(BorderStyle.MEDIUM);
        Font font = StyleUtil.createFont(writer.getWorkbook(), color, fontSize, fontName);
        cellStyle.setFont(font);
        cellStyle.setWrapText(wrapText);
        return cellStyle;
    }

    public static Font getUnderlineFont(Workbook workbook) {
        Font font = StyleUtil.createFont(workbook, (short) -1, (short) 14, "宋体");
        font.setUnderline(org.apache.poi.ss.usermodel.Font.U_SINGLE); //下划线
        return font;
    }

    public static void exportPaymentApproval(PaymentApprovalInfo approvalInfo, File targetFile) {
        ExcelWriter writer = ExcelUtil.getWriter(targetFile);
        Cell curDateCell = writer.getCell(16, 3, true);
        curDateCell.setCellValue(curDateCell.getStringCellValue().replace("${curDate}", approvalInfo.getCurDate()));

        Cell supplierNameCell = writer.getCell(1, 4, true);
        supplierNameCell.setCellValue(supplierNameCell.getStringCellValue().replace("${supplierName}", approvalInfo.getSupplierName()));

        Cell bankAccountCell = writer.getCell(8, 4, true);
        bankAccountCell.setCellValue(bankAccountCell.getStringCellValue().replace("${bankAccount}", null == approvalInfo.getBankAccount() ? "" : approvalInfo.getBankAccount()));

        Cell bankNameCell = writer.getCell(8, 5, true);
        bankNameCell.setCellValue(bankNameCell.getStringCellValue().replace("${bankName}", null == approvalInfo.getBankName() ? "" : approvalInfo.getBankName()));

        Cell paymentAmountInWordsCell = writer.getCell(1, 6, true);
        paymentAmountInWordsCell.setCellValue(paymentAmountInWordsCell.getStringCellValue().replace("${payAmountInWords}", approvalInfo.getPayAmountInWords()));

        int contractCodeCol = 12;
        Cell payAmountCell = writer.getCell(7, contractCodeCol + 1, true);
        payAmountCell.setCellValue(payAmountCell.getStringCellValue().replace("${payAmount}", approvalInfo.getPayAmount().toString()).replace("${paymentDate}", approvalInfo.getPaymentDate()));

        setPaymentValue(writer, approvalInfo.getPayAmount());

        Cell usageCell = writer.getCell(1, 8, true);
        StringBuilder usageInfo = new StringBuilder(approvalInfo.getProjectName());
        if (null != approvalInfo.getInvoice()) {
            usageInfo.append("  发票号码：" + approvalInfo.getInvoice());
        }
        if (null != approvalInfo.getProportion() && approvalInfo.getProportion().compareTo(new BigDecimal(100)) < 0) {
            usageInfo.append("  总金额：" + approvalInfo.getTotalAmount() + "元 ，" + approvalInfo.getProportion() + "% ");
        }

        usageCell.setCellValue(usageCell.getStringCellValue().replace("${usageInfo}", usageInfo.toString()).replace("${equipments}", approvalInfo.getEquipments()));
        Cell contractCodeCell = writer.getCell(2, contractCodeCol, true);
        contractCodeCell.setCellValue(approvalInfo.getContractCode());

        Cell contactPersonCell = writer.getCell(7, contractCodeCol, true);
        contactPersonCell.setCellValue(null == approvalInfo.getContactPerson() ? "" : approvalInfo.getContactPerson());

        Cell contactPhoneCell = writer.getCell(4, contractCodeCol, true);
        contactPhoneCell.setCellValue(null == approvalInfo.getContactPhone() ? "" : approvalInfo.getContactPhone());

        Cell aogSituationCell = writer.getCell(13, contractCodeCol, true);
        aogSituationCell.setCellValue(approvalInfo.getAogSituation());

        Cell orderCodeCell = writer.getCell(2, contractCodeCol + 2, true);
        orderCodeCell.setCellValue(approvalInfo.getOrderCode());


        writer.close();

    }

    private static void setPaymentValue(ExcelWriter writer, BigDecimal payAmount) {
        int xstart = 7;
        int y = 7;
        boolean hasValue = false;
        for (int i = 0; i < 10; i++) {
            double dividend = Math.pow(10, 7 - i);
            if (!hasValue && payAmount.doubleValue() >= dividend) {
                int value = payAmount.divide(new BigDecimal(dividend), 4, BigDecimal.ROUND_HALF_UP).intValue() % 10;
                Cell x = writer.getCell(xstart + i, y, true);
                x.setCellValue(String.valueOf(value));
                if (i > 0) {
                    Cell temp = writer.getCell(xstart + i - 1, y, true);
                    temp.setCellValue("￥");
                }
                hasValue = true;
            } else if (hasValue) {
                int value = payAmount.divide(new BigDecimal(dividend), 4, BigDecimal.ROUND_HALF_UP).intValue() % 10;
                Cell x = writer.getCell(xstart + i, y, true);
                x.setCellValue(String.valueOf(value));
            }
        }
    }

    public static void exportPurchaseSupplier(String startDate, String endDate, List<PurchaseSupplierDetailExcelBo> excelBos, File targetFile) {
        ExcelWriter writer = ExcelUtil.getWriter(targetFile);

        //设置样式
        StyleSet styleSet = createDefaultStyleSet(writer);
        writer.setStyleSet(styleSet);

        int[] startDatePos = new int[]{1, 0};
        int[] endDatePos = new int[]{3, 0};
        int[] totalPricePos = new int[]{12, 3};

        Map<String, List<Integer>> mergedColMap = new HashMap<>();
        mergedColMap.put("orderCode", Arrays.asList(0, 1));
        mergedColMap.put("orderUsage", Arrays.asList(2));
        mergedColMap.put("contractCode", Arrays.asList(3, 4, 5, 6, 7));

        //替换 startDate, endDate;
        Cell startDateCell = writer.getCell(startDatePos[0], startDatePos[1], true);
        startDateCell.setCellValue(startDateCell.getStringCellValue().replace("${startDate}", null == startDate ? "" : startDate));

        Cell endDateCell = writer.getCell(endDatePos[0], endDatePos[1], true);
        endDateCell.setCellValue(endDateCell.getStringCellValue().replace("${endDate}", null == endDate ? "" : endDate));

        int headerRowIndex = 2;
        BigDecimal totalPrice = new BigDecimal(0);
        if (!CollectionUtils.isEmpty(excelBos)) {
            Sheet sheet = writer.getSheet();
            Row headerRow = writer.getOrCreateRow(headerRowIndex);
            sheet.shiftRows(headerRowIndex + 1, sheet.getLastRowNum(), excelBos.size(), true, false);
            int i = headerRowIndex + 1;
            for (PurchaseSupplierDetailExcelBo excelBo : excelBos) {
                Row dataRow = writer.getOrCreateRow(i);
                dataRow.setHeight(headerRow.getHeight());
                //写入行数据
                insertRowData(dataRow, headerRow, excelBo, supplierTitleMap, writer.getCellStyle());
                totalPrice = totalPrice.add(excelBo.getMaterialTotalPrice());
                i++;
            }
            //替换totalPrice
            Cell totalPriceCell = writer.getCell(totalPricePos[0], totalPricePos[1] + excelBos.size(), true);
            totalPriceCell.setCellValue(totalPriceCell.getStringCellValue().replace("${totalPrice}", totalPrice.toString()));

            //合并单元格
            mergeColumnData(mergedColMap, excelBos, headerRowIndex + 1, writer);
        } else {
            //清空totalPrice
            Cell totalPriceCell = writer.getCell(totalPricePos[0], totalPricePos[1] + excelBos.size(), true);
            totalPriceCell.setCellValue(totalPriceCell.getStringCellValue().replace("${totalPrice}", ""));

        }
        writer.close();

    }

    public static void exportPurchaseProject(PurchaseProjectVo projectVo, File targetFile) {
        ExcelWriter writer = ExcelUtil.getWriter(targetFile);

        //设置样式
        StyleSet styleSet = createDefaultStyleSet(writer);
        writer.setStyleSet(styleSet);

        int[] projectNamePos = new int[]{1, 0};
        int[] projectAmountPos = new int[]{6, 0};

        Map<String, List<Integer>> mergedColMap = new HashMap<>();
        mergedColMap.put("orderCode", Arrays.asList(0, 1));
        mergedColMap.put("supplierName", Arrays.asList(2));
        mergedColMap.put("contractCode", Arrays.asList(3, 4, 5, 6, 7));

        //替换 project
        Cell projectNameCell = writer.getCell(projectNamePos[0], projectNamePos[1], true);
        projectNameCell.setCellValue(projectNameCell.getStringCellValue().replace("${projectName}", projectVo.getProjectName()));

        Cell projectAmountCell = writer.getCell(projectAmountPos[0], projectAmountPos[1], true);
        projectAmountCell.setCellValue(projectAmountCell.getStringCellValue().replace("${projectAmount}", projectVo.getProjectAmount().toString()));

        int headerRowIndex = 2;
        BigDecimal totalPrice = new BigDecimal(0);
        List<PurchaseOrderDetailVo> detailVos = projectVo.getDetails();
        if (!CollectionUtils.isEmpty(detailVos)) {
            Sheet sheet = writer.getSheet();
            Row headerRow = writer.getOrCreateRow(headerRowIndex);
            sheet.shiftRows(headerRowIndex + 1, sheet.getLastRowNum(), detailVos.size(), true, false);
            int i = headerRowIndex + 1;
            for (PurchaseOrderDetailVo excelBo : detailVos) {
                Row dataRow = writer.getOrCreateRow(i);
                dataRow.setHeight(headerRow.getHeight());
                //写入行数据
                insertRowData(dataRow, headerRow, excelBo, projectTitleMap, writer.getCellStyle());
                totalPrice = totalPrice.add(excelBo.getMaterialTotalPrice());
                i++;
            }
            //合并单元格
            mergeColumnData(mergedColMap, detailVos, headerRowIndex + 1, writer);
        }
        writer.close();

    }

    public static void exportPurchaseOrderDetail(List<Map<String, Object>> excelBos, File targetFile) {
        ExcelWriter writer = ExcelUtil.getWriter(targetFile);

        int headerRowIndex = 0;
        Map<String, List<Integer>> mergedColMap = new HashMap<>();
        mergedColMap.put("contractCode", Arrays.asList(detailFieldAry.indexOf("contractCode"), detailFieldAry.indexOf("invoice"), detailFieldAry.indexOf("paymentSituation"), detailFieldAry.indexOf("contractSubAmount")));
        mergedColMap.put("orderCode", Arrays.asList(detailFieldAry.indexOf("orderCode"), detailFieldAry.indexOf("orderDate")));

        //插入内容
        if (!CollectionUtils.isEmpty(excelBos)) {
            int i = headerRowIndex + 1;
            for (Map<String, Object> excelMap : excelBos) {
                Row dataRow = writer.getOrCreateRow(i);
                //写入行数据
                insertRowData(dataRow, excelMap, detailFieldAry, writer.getCellStyle());
                i++;
            }
            //合并单元格
            mergeColumnData(mergedColMap, excelBos, headerRowIndex + 1, writer);
        }
        writer.close();
    }

    public static void exportPurchaseOrder(PurchaseOrder order, List<PurchaseOrderExcelBo> excelBos, File targetFile) {
        ExcelWriter writer = ExcelUtil.getWriter(targetFile);
        //设置样式
        StyleSet styleSet = createDefaultStyleSet(writer);
        styleSet.setFont((short) -1, (short) 22, "宋体", true);
        writer.setStyleSet(styleSet);

        int[] orderCodePos = new int[]{15, 4};
        int[] orderDatePos = new int[]{11, 6};
        int headerRowIndex = 7;
        int[] totalPricePos = new int[]{11, 10};
        Map<String, List<Integer>> mergedColMap = new HashMap<>();
        mergedColMap.put("orderUsage", Arrays.asList(12));
        mergedColMap.put("indentCode", Arrays.asList(13));
        mergedColMap.put("supplier", Arrays.asList(15));

        //替换orderCode;
        Cell codeCell = writer.getCell(orderCodePos[0], orderCodePos[1], true);
        codeCell.setCellValue(codeCell.getStringCellValue().replace("${orderCode}", order.getOrderCode()));

        //替换orderDate
        Cell dateCell = writer.getCell(orderDatePos[0], orderDatePos[1], true);
        String oDate = "";
        if (!StringUtils.isEmpty(order.getOrderDate())) {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd日");
            oDate = sdf.format(order.getOrderDate());
        }
        dateCell.setCellValue(dateCell.getStringCellValue().replace("${orderDate}", oDate));

        //插入内容
        if (!CollectionUtils.isEmpty(excelBos)) {
            Sheet sheet = writer.getSheet();
            Row headerRow = writer.getOrCreateRow(headerRowIndex);
            sheet.shiftRows(headerRowIndex + 1, sheet.getLastRowNum(), excelBos.size(), true, false);
            int i = headerRowIndex + 1;
            BigDecimal totalPrice = new BigDecimal(0);
            for (PurchaseOrderExcelBo excelBo : excelBos) {
                Row dataRow = writer.getOrCreateRow(i);
                dataRow.setHeight(headerRow.getHeight());
                //写入行数据
                insertRowData(dataRow, headerRow, excelBo, orderTitleMap, writer.getCellStyle());
                totalPrice = totalPrice.add(excelBo.getEquipmentTotalPirce());
                i++;
            }

            //替换totalPrice
            Cell totalPriceCell = writer.getCell(totalPricePos[0], totalPricePos[1] + excelBos.size(), true);
            totalPriceCell.setCellValue(totalPriceCell.getStringCellValue().replace("${totalPrice}", totalPrice.toString()));

            //合并单元格
            mergeColumnData(mergedColMap, excelBos, headerRowIndex + 1, writer);

        } else {
            //清空totalPrice
            Cell totalPriceCell = writer.getCell(totalPricePos[0], totalPricePos[1] + excelBos.size(), true);
            totalPriceCell.setCellValue(totalPriceCell.getStringCellValue().replace("${totalPrice}", ""));
        }

        writer.close();
    }

    private static StyleSet createDefaultStyleSet(ExcelWriter writer) {
        StyleSet styleSet = new StyleSet(writer.getWorkbook());
        //细黑边框
        styleSet.setBorder(BorderStyle.THIN, IndexedColors.BLACK);
        //自动换行
        styleSet.setWrapText();
        return styleSet;
    }

    /**
     * 按列合并内容相同的相邻单元格
     *
     * @param mergedColMap
     * @param excelBos
     * @param startRowIndex
     * @param writer
     * @param <T>
     */
    private static <T> void mergeColumnData(Map<String, List<Integer>> mergedColMap, List<T> excelBos, int startRowIndex, ExcelWriter writer) {
        //合并单元格
        for (Map.Entry<String, List<Integer>> entry : mergedColMap.entrySet()) {
            List<Integer> usageMergeList = splitExcelBos(excelBos, entry.getKey());
            int firstRow = startRowIndex;
            List<Integer> mergeColIndex = entry.getValue();
            for (Integer stepLength : usageMergeList) {
                int lastRow = firstRow - 1 + stepLength;
                if (stepLength > 1) {
                    //多于一格的需要合并
                    for (Integer colIdx : mergeColIndex) {
                        Object content = CellUtil.getCellValue(writer.getCell(colIdx, firstRow));
                        writer.merge(firstRow, lastRow, colIdx, colIdx, content, false);
                    }
                }
                firstRow = lastRow + 1;
            }
        }
    }

    /**
     * 将bean对象写入目标行
     *
     * @param dataRow
     * @param headerRow
     * @param excelBo
     * @param titleMap
     */
    private static void insertRowData(Row dataRow, Row headerRow,
                                      Object excelBo, HashMap<String, String> titleMap,
                                      CellStyle cellStyle) {
        for (int j = headerRow.getFirstCellNum(); j < headerRow.getLastCellNum(); j++) {
            String headerColStr = headerRow.getCell(j).getStringCellValue();
            if (StringUtils.isEmpty(headerColStr)) {
                continue;
            }
            Cell newCell = dataRow.createCell(j);
            CellUtil.setCellValue(newCell, BeanUtil.getFieldValue(excelBo, titleMap.get(headerColStr)), cellStyle);
        }
    }

    /**
     * 将Map写入目标行
     *
     * @param dataRow
     * @param objMap
     * @param titleList
     * @param cellStyle
     */
    private static void insertRowData(Row dataRow, Map<String, Object> objMap, List<String> titleList,
                                      CellStyle cellStyle) {
        for (int j = 0; j < titleList.size(); j++) {
            String field = titleList.get(j);
            if (StringUtils.isEmpty(field)) {
                continue;
            }
            Cell newCell = dataRow.createCell(j);
            CellUtil.setCellValue(newCell, objMap.get(field), cellStyle);
        }
    }

    public static <T> List<Integer> splitExcelBos(List<T> excelBos, String fieldName) {
        List<Integer> res = new ArrayList<>();
        if (CollectionUtils.isEmpty(excelBos)) {
            return res;
        }
        int pos = 0;
        if (CollectionUtils.isEmpty(excelBos)) {
            return res;
        }
        Object value = BeanUtil.getFieldValue(excelBos.get(0), fieldName);
        res.add(1);
        for (int i = 1; i < excelBos.size(); i++) {
            T excelBo = excelBos.get(i);
            Object newValue = BeanUtil.getFieldValue(excelBo, fieldName);
            if (null != value && value.equals(newValue)) {
                //值相等， +1
                res.set(pos, res.get(pos) + 1);
            } else {
                pos += 1;
                value = newValue;
                res.add(1);
            }
        }
        return res;
    }

    public static Map<PurchaseOrder, List<PurchaseOrderExcelBo>> readPurchaseOrder(File tgtFile) {
        ExcelReader reader = ExcelUtil.getReader(tgtFile, 0);
        List<List<Object>> objRows = reader.read(0, reader.getRowCount());
        Map<PurchaseOrder, List<PurchaseOrderExcelBo>> result = new HashMap<>();
        PurchaseOrder key = null;
        List<PurchaseOrderExcelBo> value = null;
        List<Object> titleRow = null;
        boolean findStart = false;
        for (int rowNum = 0; rowNum < objRows.size(); rowNum++) {
            List<Object> row = objRows.get(rowNum);

            List<Object> validRow = row.stream().filter(e -> !StringUtils.isEmpty(e)).collect(Collectors.toList());
            if (CollectionUtils.isEmpty(validRow)) {
                continue;
            }
            if (!findStart && validRow.stream().filter(e -> !StringUtils.isEmpty(e.toString().trim()) && e.toString().contains(PURCHASE_ORDER_START)).count() > 0) {
                //找到start
                findStart = true;
                Object purchaseCode = validRow.stream().filter(e -> !StringUtils.isEmpty(e.toString().trim()) && !e.toString().contains(PURCHASE_ORDER_START)).collect(Collectors.toList()).get(0);
                //初始化 key value
                if (StringUtils.isEmpty(purchaseCode)) {
                    throw new AppException(MessageFormat.format("行-{0} 缺少审批审批单号", rowNum + 1));
                }
                key = new PurchaseOrder();
                key.setOrderCode(purchaseCode.toString());
                value = new ArrayList<>();

            }
            if (findStart) {

                if (validRow.stream().filter(e -> null != e && (e.toString().contains(PURCHASE_ORDER_END1) || e.toString().contains(PURCHASE_ORDER_END2))).count() > 0) {
                    //找到 end
                    result.put(key, value);
                    findStart = false;
                } else {
                    //查找日期
                    if (StringUtils.isEmpty(key.getOrderDate())) {
                        List<Object> dates = validRow.stream().filter(e -> e.toString().trim().startsWith(PURCHASE_ORDER_DATE)).collect(Collectors.toList());
                        if (!CollectionUtils.isEmpty(dates)) {
                            String dateStr = dates.get(0).toString().trim();
                            String[] dateAry = dateStr.split(":");

                            if (dateAry.length > 1) {
                                key.setOrderDate(Date.valueOf(parse2LocalDate(dateAry[1].trim().replace(" ", ""))));
                            } else {
                                dateAry = dateStr.split("：");
                                if (dateAry.length > 1) {
                                    key.setOrderDate(Date.valueOf(parse2LocalDate(dateAry[1].trim().replace(" ", ""))));
                                } else {
                                    throw new AppException("解析日期错误-" + dateStr);
                                }
                            }
                        }
                    }
                    //查找 titleRow
                    if (isTitleRow(validRow)) {
                        titleRow = row;
                    } else {
                        Map<String, Object> tempMap = rowToMap(row, titleRow);
                        PurchaseOrderExcelBo excelBo = BeanUtil.mapToBean(tempMap, PurchaseOrderExcelBo.class, true);
                        //判断是否有效
                        if (!StringUtils.isEmpty(excelBo.getXh()) && !StringUtils.isEmpty(excelBo.getSupplier()) && !StringUtils.isEmpty(excelBo.getEquipmentName())
                                && !StringUtils.isEmpty(excelBo.getEquipmentUnitPrice())) {
                            value.add(excelBo);
                        }
                    }

                }
            }
        }
        if (findStart) {
            //未找到结束标识，请确认模板是否正确
            throw new BaseException("未找到结束标识，请确认模板是否正确");
        }
        return result;
    }

    public static LocalDate parse2LocalDate(String d) {
        try {
            return LocalDate.parse(d, formatter);
        } catch (Exception ex) {
            throw new AppException(d + "-日期格式错误，例如：xxxx年x月x日");
        }
    }

    public static boolean isTitleRow(List<Object> row) {
        if (!CollectionUtils.isEmpty(row)) {
            Set<String> titleFieldSet = row.stream().filter(e -> e != null).map(e -> e.toString()).collect(Collectors.toSet());
            Collection<String> inters = CollectionUtil.intersection(orderTitleMap.keySet(), titleFieldSet);
            if (inters.size() >= 9) {
                return true;
            }
            if (orderTitleMap.keySet().containsAll(titleFieldSet)) {
                return true;
            }
        }
        return false;
    }

    public static <T> List<T> readFrom(Class<T> beanClass, File tgtFile) {
        //解析文件内容
        ExcelReader reader = ExcelUtil.getReader(tgtFile, 0);
        int titleRowNum = findTitleRow(reader);
        List<Object> title = reader.readRow(titleRowNum);
        List<List<Object>> objRows = reader.read(titleRowNum + 1, reader.getRowCount());
        List<T> result = new ArrayList<>();
        for (List<Object> row : objRows) {
            Map<String, Object> temp = rowToMap(row, title);
            T t = BeanUtil.mapToBean(temp, beanClass, true);
            result.add(t);
        }
        return result;
    }

    public static Map<String, Object> rowToMap(List<Object> row, List<Object> title) {
        Map<String, Object> result = new HashMap<>();
        if (!CollectionUtils.isEmpty(title)) {
            for (int i = 0; i < title.size(); i++) {
                if (null != title.get(i) && row.size() > i) {
                    String key = title.get(i).toString();
                    result.put(orderTitleMap.get(key), row.get(i));
                }
            }
        }
        return result;
    }


    public static int findTitleRow(ExcelReader reader) {
        boolean hasTitle = false;
        int titleRow = 0;
        for (int i = 0; i < reader.getRowCount(); i++) {
            List<Object> title = reader.readRow(i);
            if (!CollectionUtils.isEmpty(title)) {
                Set<String> titleFieldSet = title.stream().filter(e -> e != null).map(e -> e.toString()).collect(Collectors.toSet());
                if (orderTitleMap.keySet().containsAll(titleFieldSet)) {
                    hasTitle = true;
                    titleRow = i;
                    break;
                }
            }
        }
        if (!hasTitle) {
            throw new AppException("excel未找到标题行");
        }
        return titleRow;
    }

}
